Plug&Join: An easy-to-use Generic Algorithm for Efficiently Processing Equi and Non-Equi Joins

This paper presents Plug&Join, a new generic algorithm for efficiently processing a broad class of different types of joins in an extensible database system. Plug&Join is not only designed to support equi joins, temporal joins, spatial joins, subset joins and other types of joins, but in contrast to previous algorithms it can be easily customized and it allows efficient processing of new types of joins that might be of relevance in the near future. Depending on the join predicate (and the data types of the join relations) Plug&Join is called with a suitable type of index structure as a parameter. Fortunately, custom types of index structures can be implemented easily under frameworks like GiST which simplifies and extends the applicability of our approach. Plug&Join partitions both join relations recursively until each partition of the inner relation fits in main memory. If an inner partition fits in memory, the algorithm builds a memory resident index of the desired type on the inner partition and probes all tupels of the corresponding outer partition against the index. Otherwise, a memory resident index is created by sampling the inner partition. The index is then used as a partitioning function for both partitions. In order to demonstrate the flexibility of Plug&Join, we present how to implement equi joins, spatial joins and subset joins by using memory resident B+-trees, R-trees and S-trees, respectively. Moreover, results obtained from different experiments for the spatial join show that Plug&Join is competitive to special-purpose methods like the Partition Based Spatial-Merge Join algorithm (PBSM).